Computer Programming: This Book Includes: Learn Python + SQL Programming + Arduino Programming by Parker Damon

Author:Parker, Damon [Parker, Damon]
Language: eng
Format: epub
Publisher: UNKNOWN
Published: 2020-06-21T16:00:00+00:00

SELECT

pdctNam, sale

FROM

(SELECT

pdctCode,

ROUND(SUM(qtyOrdrd * pricEch)) sale

FROM

orderdetail

INNER JOIN order USING (orderNo)

WHERE

YEAR(shippdDte) = 2013

GROUP BY pdctCode

ORDER BY sale DESC

LIMIT 6) top6products2013

INNER JOIN

product USING (pdctCode);"

The query above will result in the output similar to the one shown in the picture below:

To drive this concept home, review a relatively complicated example of derived tables below:

Assume that the clients from the year 2019 have to be classified into three groups: platinum, gold and silver. Moreover, taking into consideration the criteria below, you would like to calculate the number of clients within each group:

• Platinum clients with orders larger than 99K in quantity.

• Gold clients with quantity orders ranging from 9K to 99K.

• Silver clients with orders less than 9K in quantity.

To generate a query for this analysis, you must first classify each client into appropriate group using CASE clause and GROUP BY function as shown in the syntax below:

SELECT

clientNo,

ROUND(SUM(qtyOrdrd * pricEch)) sale,

(CASE

WHEN SUM(qtyOrdrd * pricEch) < 9000 THEN 'Silver'

WHEN SUM(qtyOrdrd * pricEch) BETWEEN 9000 AND 99000 THEN 'Gold'

WHEN SUM(qtyOrdrd * pricEch) >99000 THEN 'Platinum'

END) clientGroup

FROM

orderdetail

INNER JOIN

orders USING (orderNo)

WHERE

YEAR(shippdDte) = 2019

GROUP BY clientNo;

The query output will be similar to the one shown in the picture below:

Now, you can execute the code below to generate a derived table and group the clients as needed:

SELECT

clientGroup,

COUNT(cg.clientGroup) AS grpCount

FROM

(SELECT

clientNo,

ROUND(SUM(qtyOrdrd * pricEch)) sales,

(CASE

WHEN SUM(qtyOrdrd * pricEch) <9000 THEN 'Silver' WHEN SUM(qtyOrdrd * pricEch) BETWEEN 9000 AND 99000 THEN 'Gold'

WHEN SUM(qtyOrdrd * pricEch) >99000 THEN 'Platinum'

END) clientGroup

FROM

orderdetail

INNER JOIN orders USING (orderNo)

WHERE

YEAR(shippdDte) = 2019

GROUP BY clientNo) cg

GROUP BY cg.clientGroup; "

The query output will be similar to the one shown in the picture below:

Chapter 3: Advanced SQL Functions

The most common command used in SQL is the SELECT command, which we have already used in the exercises mentioned earlier in this book. So, now let me give you a deep dive into the SQL SELECT command and various clauses that can be used with it.

MySQL SELECT

You can selectively fetch desired data from tables or views, using the SELECT statement. As you already know, similar to a spreadsheet, a table comprises rows and columns. You are highly likely to view a subset of columns, a subset of rows, or a combo of them both. The outcome of this query is known as a "result set,” which are lists of records comprising the same number of columns per record.

In the picture shown below of the employee table from the "MySQL sample database”, the table has 8 different columns and several records, as shown in the picture below:

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.